""""""
import csv
import httpx
import psycopg2
# import pandas as pd
# import json


cn = psycopg2.connect(
    dbname="xt_base_test",
    user="postgres",
    password="zcpt123_#Shuyo",
    host="172.16.68.183",
    port="5432"
)

URL = "https://172.16.67.121"

def process_csv():
    """处理招标人Sheet1.csv文件"""
    with open('招标人Sheet1.csv', 'r', encoding='utf-8') as f:
        reader = csv.reader(f)
        next(reader)

        credit_list = []
        for row in reader:
            
            # 统一社会信用代码为空 保存到 Excel 中
            if len(row[7].strip()) == 0:
                with open('招标人Sheet1_统一社会信用代码为空.csv', 'a', encoding='utf-8', newline='') as f:
                    writer = csv.writer(f)
                    writer.writerow(row)
            else:
                
                # 统一社会信用代码不为空 排除重复的统一社会信用代码 
                if  (credit_list.count(row[7].strip()) < 1):
                    credit_code = row[7].strip()

                    credit_list.append(credit_code)

                    headers = {
                        "Authorization": "eeedf00d-0927-4957-98cc-b52d2baeb1e9"
                    }
                    
                    response = httpx.get(url=f"http://open.api.tianyancha.com/services/open/ic/baseinfo/normal?keyword={credit_code}", headers=headers)
                    
                    if (response.status_code == 200):
                
                        
                        data = response.json()

                        if (data["error_code"] == 0):
                            
                            result = data["result"]
                            
                            # 写入 excel
                            with open('招标人Sheet1_企业基本信息.csv', 'a', encoding='utf-8', newline='') as f1:
                                writer = csv.writer(f1)
                                writer.writerow((
                                    result["name"],
                                    result["regLocation"],
                                    result["industry"],
                                    result["staffNumRange"],
                                    result["regNumber"], 
                                    result["creditCode"],
                                    result["orgNumber"],
                                    result["legalPersonName"],

                                    row[4],
                                    row[5],
                                    ))
                                
                            with open('供应商Sheet1_企业基本信息.csv', 'a', encoding='utf-8', newline='') as f2:
                                writer = csv.writer(f2)
                                writer.writerow((
                                    result["name"],
                                    result["property3"],
                                    result["alias"],
                                    result["companyOrgType"],
                                    result["estiblishTime"], 
                                    result["businessScope"],
                                    result["socialStaffNum"],
                                    result["industry"],
                                    result["regInstitute"],
                                    result["approvedTime"],
                                    result["staffNumRange"],
                                    result["legalPersonName"],
                                    result["taxNumber"],
                                    result["regCapitalCurrency"],
                                    result["actualCapital"],
                                    result["regLocation"],
                                    result["regNumber"], 
                                    result["orgNumber"],
                                    row[4],
                                    row[5],
                                    ))
                        else:
                            # 写入错误信息
                            with open('招标人Sheet1_错误的企业基本信息.csv', 'a', encoding='utf-8', newline='') as f3:
                                writer = csv.writer(f3)
                                writer.writerow((credit_code, data["error_code"], data["reason"]))
                            print(data["reason"])
                          

def distinct_corp():
    """去重企业"""
    with open('招标人Sheet1_企业基本信息.csv', 'r', encoding='utf-8') as f:
        reader = csv.reader(f)

        corp_list = []
        for row in reader:
            if corp_list.count(row[5]) < 1:
                corp_list.append(row[5])

                with open('招标人Sheet1_企业基本信息_去重.csv', 'a', encoding='utf-8', newline='') as f:
                    writer = csv.writer(f)
                    writer.writerow(row)


def distinct_user():
    """去重用户"""
    with open('招标人Sheet1_用户信息.csv', 'r', encoding='utf-8') as f:
        reader = csv.reader(f)

        user_list = []
        for row in reader:
            if user_list.count((row[0], row[1])) < 1:
                user_list.append((row[0], row[1]))

                with open('招标人Sheet1_用户信息_去重.csv', 'a', encoding='utf-8', newline='') as f:
                    writer = csv.writer(f)
                    writer.writerow(row)


def create_corp():
    """创建企业"""

    headers = {
        "Content-Type": "application/json",
    }


    with open('招标人Sheet1_企业基本信息_去重_copy.csv', 'r', encoding='utf-8') as f:

        reader = csv.reader(f)

        for row in reader:
            payload = {
                "name": row[0].replace(" ", ""),
                "area": row[1],
                "industry": row[2],
                "scale": row[3],
                "registrationNum": row[4],
                "unifiedSocialCredit": row[5],
                "organizationCode": row[6],
                "legalPerson": row[7],
                "licenseUrl": row[8],
                "licenseName": row[9],
                "desc": "tag-import",
                # "userUuid": "486ffe89-6697-4ce6-9cab-600ce253c8ba",
                "userUuid": "bbae9b9f-9018-4046-b686-22eb2c908a0d",
            }
            
            resp = httpx.post(url=f"{URL}/api/base/v1/corporation/create", headers=headers, json=payload, verify=False)

            if resp.status_code == 200:
                print(resp.json())
            else:
                print(resp.status_code)
                print(resp.text)


def create_user():
    """创建用户"""
    headers = {
        "Content-Type": "application/json",
    }

    with open('招标人Sheet1_用户信息_去重.csv', 'r', encoding='utf-8') as f:
        reader = csv.reader(f)

        for row in reader:

            if len(row[2]) > 1:
                payload = {
                    "name": row[0].replace(" ", ""),
                    "account": row[1].replace(" ", ""),
                    "idcard": row[2].replace(" ", ""),
                    "code": "",
                    "password": "123456789"
                }

                resp = httpx.post(url=f"{URL}/api/base/v2/admin/register", headers=headers, json=payload, verify=False)

                if resp.status_code == 200:
                    print(resp.json())
                else:
                    with open('招标人Sheet1_用户信息_错误.csv', 'a', encoding='utf-8', newline='') as f1:
                        writer = csv.writer(f1)
                        writer.writerow((row[0], row[1], row[2], row[3], row[4], resp.status_code, resp.text))
            else:
                with open('招标人Sheet1_用户信息_错误.csv', 'a', encoding='utf-8', newline='') as f1:
                    writer = csv.writer(f1)
                    writer.writerow((row[0], row[1], row[2], row[3], row[4], "身份证号码为空"))


def corp_user_relation():
    """企业用户关联"""
    headers = {
        "Content-Type": "application/json",
    }

    with open('招标人Sheet1_用户信息_去重.csv', 'r', encoding='utf-8') as f:
        reader = csv.reader(f)

        for row in reader:

            with cn.cursor() as cur:
                cur.execute("SELECT uuid FROM corporations WHERE unified_social_credit = %s", (row[4],))
                result = cur.fetchone()

                if result is not None:

                    resp = httpx.get(url=f"{URL}/api/base/v1/user/mobile?mobile={row[1]}", headers=headers, verify=False)
                    
                    if resp.status_code == 200:
                        
                        roles = resp.json()

                        if roles['errcode'] == 0:
                            for role in roles['result']['list']:
                                if len(role['userUuid']) > 1:

                                    payload = {
                                        "corpId": result[0],
                                        "userUuid": role['userUuid'],
                                    }

                                    resp1 = httpx.post(url=f"{URL}/api/base/v1/user/joincorp", headers=headers, json=payload, verify=False)

                                    if resp1.status_code == 200:
                                        print(resp.json())
                                    else:
                                        with open('招标人Sheet1_用户信息_错误.csv', 'a', encoding='utf-8', newline='') as f1:
                                            writer = csv.writer(f1)
                                            writer.writerow((row[0], row[1], row[2], row[3], row[4], resp.status_code, resp.text))
                else:
                    with open('招标人Sheet1_用户信息_企业统一社会信用代码不存在.csv', 'a', encoding='utf-8', newline='') as f1:
                        writer = csv.writer(f1)
                        writer.writerow((row[0],row[1], row[2], row[3], row[4], "企业统一社会信用代码不存在"))


def corp_relation():
    """创建企业关联"""

    headers = {
        "Content-Type": "application/json",
    }

    with cn.cursor() as cur:
        
        cur.execute("""
            select 
                distinct 
                    c.name,
                    c.uuid,
                    c.license_url,
                    c.license_name,
                    c.unified_social_credit
            from 
                corporations c 
            where 
                c.license_name != '';
        """)
        result = cur.fetchall()

        if result is not None:

            for item in result:
                
                cur.execute("select uuid from corporations where license_url = %s", (item[3],))
                d = cur.fetchone()

                if d is not None:
                    
                    payload = {
                        "superiorCorpId": d[0],
                        "subCorpId": item[1],
                        "joinType": 1,
                    }

                    resp = httpx.post(url=f"{URL}/api/base/v1/linkedcorp/create", headers=headers, json=payload, verify=False)

                    if resp.status_code == 200:
                        print(resp.json())
                        data = resp.json()
                        if data['errcode'] == 0:
                            with open('招标人Sheet1_企业关联.csv', 'a', encoding='utf-8', newline='') as f:
                                writer = csv.writer(f)
                                writer.writerow((item[0], item[1], item[2], item[3], item[4]))

                else:
                    with open('招标人Sheet1_企业关联_错误.csv', 'a', encoding='utf-8', newline='') as f:
                        writer = csv.writer(f)
                        writer.writerow((item[0], item[1], item[2], item[3], item[4], "上级机构不存在"))


def user_role_relation():
    """用户关联角色"""

    with open("用户角色关联.csv", "r", encoding="utf-8") as f:
        reader = csv.reader(f)
        next(reader)
        for row in reader:

            with cn.cursor() as cur:

                user_response = httpx.get(url=f"{URL}/api/base/v1/user/mobile", params={"mobile": row[1]})

                if user_response.status_code == 200:
                    
                    if user_response.json()['errcode'] == 0:

                        cur.execute("SELECT uuid FROM corporations WHERE unified_social_credit = %s", (row[3],))
                        corp = cur.fetchone()

                        if corp is not None:

                            for user in user_response.json()['result']['list']:
                               
                                if user['corpId'] == corp[0]:
                                    
                                    parts = row[4].split(",")

                                    available_roles = []

                                    for part in parts:
                                        if part in  ["招标人自采", "采购业务员", "供应商机构管理员", "供应商业务员"]:
                                            cur.execute("SELECT uuid FROM roles WHERE name = %s", (part,))
                                            result_role = cur.fetchone()
                                            if result_role is not None:
                                                available_roles.append(result_role[0])

                                    if len(available_roles) > 0:
                                        payload = {
                                            "corpId": corp[0],
                                            "userId": user['userId'],
                                            "adminRoleIds": available_roles,
                                        }

                                        print(f"payload:{payload}")
                                        response = httpx.post(url=f"{URL}/api/base/v1/addAdminRolesForUser",json=payload, verify=False)

                                        if response.status_code == 200:
                                            print(response.json())
                                        else:
                                            print(response.status_code)
                                            print(response.text)
                                    else:
                                        with open('用户角色关联_错误.csv', 'a', encoding='utf-8', newline='') as f:
                                            writer = csv.writer(f)
                                            writer.writerow((row[0], row[1], row[2], row[3], row[4], "角色不存在"))
                                else:
                                    with open('用户角色关联_错误.csv', 'a', encoding='utf-8', newline='') as f:
                                        writer = csv.writer(f)
                                        writer.writerow((row[0], row[1], row[2], row[3], row[4], "用户不属于该企业"))
                        else:
                            with open('用户角色关联_错误.csv', 'a', encoding='utf-8', newline='') as f:
                                writer = csv.writer(f)
                                writer.writerow((row[0], row[1], row[2], row[3], row[4], "企业不存在"))
                else:
                    with open('用户角色关联_错误.csv', 'a', encoding='utf-8', newline='') as f:
                        writer = csv.writer(f)
                        writer.writerow((row[0], row[1], row[2], row[3], row[4], "用户不存在"))


# def test():

#     # 读取Excel文件
#     df = pd.read_csv('招标人Sheet1_用户信息.csv')

#     # 将姓名、电话、身份证信息和企业统一社会信用代码相同的行合并在一起，将他们的角色信息合并到一个字符串中
#     df = df.groupby(['name', 'mobile', 'id_card', 'credit_code'])['role_name'].apply(','.join).reset_index()

#     # 将DataFrame转换为CSV
#     df.to_csv('output.csv', index=False)

if __name__ == "__main__":

    # process_csv()
    # distinct_corp()
    # distinct_user()
    # corp()
    # users()
    # create_corp()
    # create_user()
    # create_link()
    # corp_user_relation()
    # corp_relation()
    # test()
    # user_role_relation()